/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * SurC.java
 *
 * Created on Nov 7, 2010, 9:53:30 PM
 */

package lmclogistics;

import components.Image;
import components.NButton;
import components.Text;
import components.TextArea;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.Vector;
import javax.swing.JComboBox;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.joda.time.DateTime;
import org.joda.time.Days;
import sql.Sql;
import java.text.SimpleDateFormat;
import java.util.Calendar;
/**
 *
 * @author Sonia Tan
 */
public class SurC extends javax.swing.JPanel {

    /** Creates new form EirSummary */
    private Image bg;
    private JTable surCTable;
    private JScrollPane scroll;
    public DefaultTableModel model;
    private TableColumn column;
    private NButton print;
    private TextArea filterField;
    private String sDate;
    private String eDate;
    private JComboBox customerField = new JComboBox();
    private String custName;


    Calendar calendar = Calendar.getInstance();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

    public SurC(String sDate, String eDate, String custName) {
        initComponents();
        this.sDate = sDate;
        this.eDate = eDate;
        this.custName = custName;
        setOpaque(false);
        setLocation(getCenterX(761), getCenterY(460));
        setSize(761, 460);
        setVisible(true);
        setBg();
        initTable();
        initButtons();
//        initFilter();
        initBox();
        showTable();
    }

    public final void setBg(){
        bg = new Image("panelb.png",761,460);

        add(bg,-1);

    }

    public int getCenterX(int x){

        int a = 919/2 - x/2;


        return a;
    }

    public int getCenterY(int x){

        int a = 577/2 - x/2;


        return a;
    }

    public void initTable(){
                surCTable = new JTable(model);
		surCTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		surCTable.setFont(new Font("Lucida Grande", Font.PLAIN, 12));
		surCTable.getTableHeader().setFont(new Font("Lucida Grande", Font.PLAIN, 12));
		scroll = new JScrollPane(surCTable);
		scroll.setBounds(20,15,721,400);
		add(scroll,0);

    }

    public void showTable(){
        
        int custCode = getCustCode();
      
        try {
            String query = "SELECT custID, outDate, outCode, rentStatus, chsCode, outTrkCode, outPltNo, outTime, outConsignee, outCwsNo, COUNT(*) as Total "
                         + "FROM Rental r WHERE custID = "+custCode+" and outDate >= '"+sDate+"' and outDate <= '"+eDate
                         + "' and rentStatus = 'out' and chsCode in (SELECT chsPin FROM Chassis WHERE chsSize = 20)";


            Connection connection = Sql.getSqlConnection();
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            String t20 = rs.getString("Total");

            query = "SELECT custName, outDate, outCode, rentStatus, chsCode, trkName, outPltNo, outTime, consFName, outCwsNo "
                         + "FROM Rental r,Customer c, Trucking t, Consignee cons WHERE c.custName = '"+custName+"' and r.custID = c.custCode and r.outTrkCode = t. trkCode and r.outConsignee = cons.consCode and outDate >= '"+sDate+"' and outDate <= '"+eDate
                         + "' and rentStatus = 'out' and chsCode in (SELECT chsPin FROM Chassis WHERE chsSize = 20)";



            rs = stmt.executeQuery(query);
           model = new DefaultTableModel(){
                public boolean isCellEditable(int row, int column) {
                    return false;
                    }
            };

            model.addColumn("Company");
            model.addColumn("Date");
            model.addColumn("REC No.");
            model.addColumn("Status");
            model.addColumn("Chassis");
            model.addColumn("Chassis Size");
            model.addColumn("Trucking");
            model.addColumn("Plate No.");
            model.addColumn("Timeout");
            model.addColumn("Consignee");
            model.addColumn("Authority");
            model.addColumn("Days");
            model.addColumn("Total");

            int first = 0;


            while(rs.next()) {
               Vector rowData = new Vector () ;


               String outDate = rs.getString("outDate");

               rowData.add(rs.getString("custName"));
               rowData.add(outDate);
               rowData.add(rs.getString("outCode"));
               rowData.add(rs.getString("rentStatus"));
               rowData.add(rs.getString("chsCode"));
               rowData.add(20);
               rowData.add(rs.getString("trkName"));
               rowData.add(rs.getString("outPltNo"));
               rowData.add(rs.getString("outTime"));
               rowData.add(rs.getString("consFName"));
               rowData.add(rs.getString("outCwsNo"));
               rowData.add(nDays(outDate));
               if(first == 0){
                rowData.add(t20);
                first = 1;
               }else
                    rowData.add("");

               model.addRow(rowData);
            }

            String query2 = "SELECT custID, outDate, outCode, rentStatus, chsCode, outTrkCode, outPltNo, outTime, outConsignee, outCwsNo, COUNT(*) as Total "
                         + "FROM Rental r WHERE r.custID = "+custCode+" and outDate >= '"+sDate+"' and outDate <= '"+eDate
                         + "' and rentStatus = 'out' and chsCode in (SELECT chsPin FROM Chassis WHERE chsSize = 40)";

            rs = stmt.executeQuery(query2);
            rs.next();
            String t40 = rs.getString("Total");

            query2 = "SELECT custName, outDate, outCode, rentStatus, chsCode, trkName, outPltNo, outTime, consFName, outCwsNo "
                         + "FROM Rental r ,Customer c, Trucking t, Consignee cons  WHERE c.custName = '"+custName+"' and r.custID = c.custCode and  r.outTrkCode = t. trkCode and r.outConsignee = cons.consCode and outDate >= '"+sDate+"' and outDate <= '"+eDate
                         + "' and rentStatus = 'out' and chsCode in (SELECT chsPin FROM Chassis WHERE chsSize = 40)";

            rs = stmt.executeQuery(query2);
            first = 0;
            while(rs.next()) {
               Vector rowData = new Vector () ;

               String outDate = rs.getString("outDate");
               rowData.add(rs.getString("custName"));
               rowData.add(outDate);
               rowData.add(rs.getString("outCode"));
               rowData.add(rs.getString("rentStatus"));
               rowData.add(rs.getString("chsCode"));
               rowData.add(40);
               rowData.add(rs.getString("trkName"));
               rowData.add(rs.getString("outPltNo"));
               rowData.add(rs.getString("outTime"));
               rowData.add(rs.getString("consFName"));
               rowData.add(rs.getString("outCwsNo"));
               rowData.add(nDays(outDate));
               if(first == 0){
                    rowData.add(t40);
                    first = 1;
               }else
                    rowData.add("");

               model.addRow(rowData);
            }


            String query3 = "SELECT custID, outDate, outCode, rentStatus, chsCode, outTrkCode, outPltNo, outTime, outConsignee, outCwsNo, COUNT(*) as Total "
                         + "FROM Rental r WHERE r.custID = "+custCode+" and outDate >= '"+sDate+"' and outDate <= '"+eDate
                         + "' and rentStatus = 'out' and chsCode in (SELECT chsPin FROM Chassis WHERE chsSize = 'c')";

            rs = stmt.executeQuery(query3);
            rs.next();
            String tC = rs.getString("Total");

            query3 = "SELECT custName, outDate, outCode, rentStatus, chsCode, trkName, outPltNo, outTime, consFName, outCwsNo "
                         + "FROM Rental r ,Customer c, Trucking t, Consignee cons  WHERE c.custName = '"+custName+"' and r.custID = c.custCode and r.outTrkCode = t. trkCode and r.outConsignee = cons.consCode and outDate >= '"+sDate+"' and outDate <= '"+eDate
                         + "' and rentStatus = 'out' and chsCode in (SELECT chsPin FROM Chassis WHERE chsSize = 'c')";

            rs = stmt.executeQuery(query3);
            first = 0;

            while(rs.next()) {
               Vector rowData = new Vector () ;

               String outDate = rs.getString("outDate");
               rowData.add(rs.getString("custName"));
               rowData.add(outDate);
               rowData.add(rs.getString("outCode"));
               rowData.add(rs.getString("rentStatus"));
               rowData.add(rs.getString("chsCode"));
               rowData.add("Combo");
               rowData.add(rs.getString("trkName"));
               rowData.add(rs.getString("outPltNo"));
               rowData.add(rs.getString("outTime"));
               rowData.add(rs.getString("consFName"));
               rowData.add(rs.getString("outCwsNo"));
               rowData.add(nDays(outDate));
               if(first == 0){
                    rowData.add(tC);
                    first = 1;
               }else
                    rowData.add("");

               model.addRow(rowData);
            }

            surCTable.setModel(model);

            column = surCTable.getColumnModel().getColumn(1);
            column.setPreferredWidth(100);
            Sql.closeSqlConnection(connection);

        } catch (Exception ex) {
            Logger.getLogger(Chassis.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getData(JTable table, int row_index, int col_index){
        return table.getModel().getValueAt(row_index, col_index).toString();
    }

    public int nDays(String date) {

        String dToday = dateFormat.format(calendar.getTime());

        String[] dateT = splitDate(dToday);
        String[] dateG = splitDate(date);



        int tYear = Integer.parseInt(dateT[0]);
        int tMonth = Integer.parseInt(dateT[1]);
        int tDay = Integer.parseInt(dateT[2]);
        int gYear = Integer.parseInt(dateG[0]);
        int gMonth = Integer.parseInt(dateG[1]);
        int gDay = Integer.parseInt(dateG[2]);

        DateTime start = new DateTime(gYear, gMonth, gDay, 0, 0, 0, 0);
        DateTime end = new DateTime(tYear, tMonth, tDay, 0, 0, 0, 0);


        Days d = Days.daysBetween(start,end);
        int days = d.getDays();


        return days;

    }

    public String[] splitDate(String date){
        String[] dateT = date.split("-");

        return dateT;
    }


    public void initButtons(){
        print = new NButton("Print", 580, 422);
        print.setSize(80,25);
        add(print,0);

        buttonlisteners();
    }

    public void buttonlisteners()
    {
        print.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent ae)
            {
                try {
                    surCTable.print();
                } catch(Exception e)
                {

                }
            }
        });
    }


    public void initFilter(){
        Text filter = new Text();

        filter.inText("Filter:", 20, 420);
        add(filter,0);
        filterField = new TextArea();
        filterField.initText(200, 25,70, 418);
        add(filterField,0);

    }
    
    public void initBox(){

        customerField.setLocation(20, 420);
        customerField.setSize(200,25);
        add(customerField, 0);
        customerField.removeAllItems();
        
        String custQuery = "SELECT custName FROM customer";


        try{
            Connection connection = Sql.getSqlConnection();
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(custQuery);
          
           customerField.insertItemAt("<Customer>", 0);
           customerField.setSelectedIndex(0);
           int i = 1;

            while(rs.next())
            {
                customerField.insertItemAt(rs.getString(1),i);
                i++;
            }
            
               
            Sql.closeSqlConnection(connection);
        } catch(Exception ex){
            Logger.getLogger(EiroutPanel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    public int getCustCode(){
        int code = 0;
          try{
            String cuQuery = "SELECT * FROM customer WHERE custName = '"+custName+"'";

            Connection connection = Sql.getSqlConnection();
            Statement stmt = connection.createStatement();
            ResultSet cs = stmt.executeQuery(cuQuery);

            cs.next();

            code = cs.getInt("custCode");
            Sql.closeSqlConnection(connection);
          } catch(Exception ex){
                Logger.getLogger(SurC.class.getName()).log(Level.SEVERE, null, ex);
          }

          System.out.println(code);
          System.out.println(custName);
          return code;
    }
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 400, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 300, Short.MAX_VALUE)
        );
    }// </editor-fold>//GEN-END:initComponents


    // Variables declaration - do not modify//GEN-BEGIN:variables
    // End of variables declaration//GEN-END:variables

}
